'''

Case Type： 数据库系统
Case Name： 主机索引文件被破坏，主机使用到索引报错，索引重建成功，备机正常
Create At:  2020/07/02
Owner    :  @li-xin12345
Descption:  1、主机索引损坏、无权限、删除 2、主机analyze，checkpoint，备机做查询和explain

history：
created： 2020/07/02  created by @li-xin12345
'''

import sys
import unittest
import time
import os
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import *
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant


class IndexFileDamaged(unittest.TestCase):
    sh_primary = CommonSH('PrimaryDbUser')
    sh_standby = CommonSH('Standby1DbUser')
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        self.log = Logger()
        self.log.info('------------Opengauss_Reliability_Dbsys_Case067.py start------------')
        self.DN1_PATH = macro.DB_INSTANCE_PATH
        self.primaryNode = Node(node='PrimaryDbUser')
        self.standbyNode = Node(node='Standby1DbUser')
        self.com = Common()
        self.Constant = Constant()

    def test_Index_file_damaged(self):
        # 新建表
        t_name = 't_test'
        t_index_name = 't_test_index'
        self.log.info('----------------------------创建表，并写入数据-----------------------------')
        msg = self.com.createLargeNumTable(self.primaryNode, t_name, 1, 100000)
        self.log.info(msg)
        # 建索引
        sql_cmd = f"create unique index {t_index_name} on {t_name}(t_id);"
        self.log.info(sql_cmd)
        msg = self.sh_primary.executDbSql(sql_cmd)
        self.log.info(msg)

        # 查询索引oid
        self.log.info('----------------------------查询索引oid-----------------------------')
        sql_cmd = f"select oid from pg_class where relname='{t_index_name}';"
        self.log.info(sql_cmd)
        msg = self.sh_primary.executDbSql(sql_cmd)
        self.log.info(msg)
        oid = msg.splitlines()[-2].strip()

        # 通过oid查询索引文件位置
        self.log.info('----------------------------通过oid查询索引文件位置-----------------------------')
        sql_cmd = f"select pg_relation_filepath({oid}::regclass);"
        self.log.info(sql_cmd)
        msg = self.sh_primary.executDbSql(sql_cmd)
        self.log.info(msg)
        pg_relation_filepath = msg.splitlines()[-2].strip()

        # 删除部分索引文件内容(删除第一行以外的内容)
        self.log.info('----------------------------删除部分索引文件内容-----------------------------')
        index_file_path = os.path.join(self.DN1_PATH, pg_relation_filepath)
        self.log.info(index_file_path)
        cmd = f"sed -i '1,$d' {index_file_path}"
        self.log.info(cmd)
        msg = self.primaryNode.sh(cmd).result()
        self.assertEqual(msg, '')

        # 重启以清除缓存
        self.log.info('----------------------------重启以清除缓存-----------------------------')
        stop_msg = self.sh_primary.stopDbCluster()
        self.assertTrue(stop_msg)
        start_msg = self.sh_primary.startDbCluster()

        self.assertTrue(start_msg)

        # 查看执行计划，以确定使用索引查询
        self.log.info('----------------------------查看执行计划，以确定使用索引查询-----------------------------')
        sql_cmd = f'explain select * from {t_name} where t_id = 999;'
        self.log.info(sql_cmd)
        msg = self.sh_primary.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn('Index Scan', msg)

        # 主机analyze，checkpoint
        self.log.info('----------------------------主机analyze，checkpoint-----------------------------')
        sql_cmd = 'analyze VERBOSE t_reason; CHECKPOINT;'
        self.log.info(sql_cmd)
        msg = self.sh_primary.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn('CHECKPOINT', msg)

        status_msg = self.sh_standby.getDbClusterStatus('status')
        self.assertTrue(status_msg)

        # 备机查询，explain
        self.log.info('----------------------------备机查询，explain-----------------------------')
        sql_cmd = f'explain select * from {t_name} where t_id = 999;'
        self.log.info(sql_cmd)
        msg = self.sh_standby.executDbSql(sql_cmd)
        self.log.info(msg)

        status_msg = self.sh_standby.getDbClusterStatus('status')
        self.assertTrue(status_msg)
        print(status_msg)

        # 重建索引以还原
        self.log.info('----------------------------重建索引以还原-----------------------------')
        sql_cmd = f'reindex index {t_index_name};'
        self.log.info(sql_cmd)
        msg = self.sh_primary.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn('REINDEX', msg)

        # 主机查询数据
        self.log.info('----------------------------主机查询数据-----------------------------')
        sql_cmd = f'select * from {t_name} where t_id = 999;'
        self.log.info(sql_cmd)
        msg = self.sh_primary.executDbSql(sql_cmd)
        self.log.info(msg)

        status_msg = self.sh_standby.getDbClusterStatus('status')
        self.assertTrue(status_msg)
        print(status_msg)

        # 备机查询数据
        self.log.info('----------------------------备机查询数据-----------------------------')
        sql_cmd = f'select * from {t_name} where t_id = 999;'
        self.log.info(sql_cmd)
        msg = self.sh_primary.executDbSql(sql_cmd)
        self.log.info(msg)

    def tearDown(self):
        # 清理环境，删除表
        msg = self.com.createLargeNumTable(self.primaryNode, 't_test', del_sign=True)
        self.log.info(msg)

        self.log.info('----------------------------用例执行完毕，后置处理-----------------------------')
        status_msg = self.sh_primary.getDbClusterStatus('status')
        if status_msg:
            pass
        else:
            status_msg = self.sh_primary.startDbCluster()
            self.assertTrue(status_msg)
